//### 作业：建一个用户表（有姓名,年龄,年龄,创建时间,修改时间）,使用模块加mysql模块,实现对数据的增删改查
//###       修改时记得，把修改时间改成修改时的时间，能实现分页（选做）
//
const { connect } = require('http2');
let mysql=require('mysql');
var connection=mysql.createConnection({
    host:"127.0.0.1",
    user:"root",
    password:"root",
    database:"user"
})
connection.connect();


//查、
// let sql="select * from user";
// connection.query(sql,(err,data)=>{
//     console.log(err);
//     console.log(data);
// });

//增
// let createTime =new Date().toLocaleString().replace('/','-').replace('/','-');
// let adddata="insert into user values (null,'大狗屎',19,'男','2022-7-4','2023-5-7')"
// connection.query(adddata,(err,data)=>{
//     console.log(err);
//     console.log(data);
// })


//改
// let updateTime=new Date().toLocaleString().replace('/','-').replace('/','-');
// let updateData="update user set name='guyguygu',updateTime='"+updateTime+"' where id=1";
// connection.query(updateData,(err,data)=>{
//     console.log(err);
//     console.log(data);
// });


//删
// let deleteData="delete from user where id=4";
// connection.query(deleteData,(err,data)=>{
//     console.log(err);
//     console.log(data);
// });



//分页
let page =2;
let pagesize=2;
let pagesql="select * from user limit "+(page-1)*pagesize+","+pagesize;
connection.query(pagesql,(err,data)=>{
    console.log(err);
    console.log(data);
});


// 实现分页,能实现男女个数和总数的统计,能统计出（小于18） 和 18-45 和 45岁以上的人数
connection.query('select count(*),sex from user group by sex',(err,data)=>{
    console.log(err);
    console.log(data);
});

let one ="select count(*) as '小于18的人数' from user where age<18";
let two="SELECT count(*) as '18-45的人数' FROM admin where age BETWEEN 18 and 45";
let three="SELECT count(*) as '大于45的人数' FROM admin where age>45";
conn.query(one,(err,data)=>{
    console.log(err);
    console.log(data);
});
conn.query(two,(err,data)=>{
    console.log(err);
    console.log(data);
});
conn.query(three,(err,data)=>{
    console.log(err);
    console.log(data);
});

conn.end();





